/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.io.File;
import java.math.BigInteger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import feedbackJAXB.generate.FeedbackList;
import feedbackJAXB.generate.Feedbacks;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import orderJAXB.generate.OrderList;
import orderJAXB.generate.Orders;
import productJAXB.generate.ProductList;
import productJAXB.generate.Products;

/**
 *
 * @author NganNLK60334
 */
public class AdminDAO {

    public String[] CheckLogin(String username, String password) throws
            ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "select * from Users INNER JOIN Roles ON Users.roleID "
                + "= Roles.roleID where username = ? and password = ? ";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        stm.setString(2, password);
        ResultSet rs = stm.executeQuery();
        String[] result = new String[6];
        if (rs.next()) {
            result[0] = "1";
            result[1] = rs.getString("username");
            result[2] = rs.getString("fullname");
            result[3] = new Boolean(rs.getBoolean("active")).toString();
            result[4] = rs.getString("roleName");
        } else {
            result[0] = "0";
        }
        rs.close();
        stm.close();
        conn.close();
        return result;
    }

    public List OrderStatus() throws
            ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "select Orders.orderID, OrderStatus.statusName from Orders INNER JOIN OrderStatus ON Orders.statusID "
                + "= OrderStatus.statusID";
        PreparedStatement stm = conn.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();
        String[] result = new String[2];
        List order = new ArrayList();
        while (rs.next()) {
            result[0] = rs.getString("orderID");
            result[1] = rs.getString("statusName");
            order.add(result);
        }
        rs.close();
        stm.close();
        conn.close();
        return order;
    }

    public void MarshallingFeedback(String context) throws ClassNotFoundException, SQLException, JAXBException {

        Feedbacks feedbacks = new Feedbacks();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        ResultSet rs = null;
        try {
            String sql = "Select * from Feedbacks";
            PreparedStatement stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();

            while (rs.next()) {
                int feedbackID = rs.getInt("feedbackID");
                String username = rs.getString("username");
                String title = rs.getString("title");
                String fbContent = rs.getString("fbContent");
                Date dateCreated = rs.getDate("dateCreated");
//                DatatypeFactory f = DatatypeFactory.newInstance();
//                XMLGregorianCalendar c = f.newXMLGregorianCalendar();
//                Date dateCreated = c.toGregorianCalendar().getTime();

                FeedbackList feedback = new FeedbackList();
                feedback.setFeedbackID(new BigInteger(Integer.valueOf(feedbackID).toString()));
                feedback.setUsername(username);
                feedback.setTitle(title);
                feedback.setFbContent(fbContent);
                feedback.setDateCreated(dateCreated);
                //GregorianCalendar Cal = new GregorianCalendar();
                //Cal.setTime(dateCreated);
                //XMLGregorianCalendar xmlCal = DatatypeFactory.newInstance().newXMLGregorianCalendar(Cal);
                //feedback.setDateCreated(xmlCal);
                feedbacks.getFeedback().add(feedback);

            }
            JAXBContext ctx = JAXBContext.newInstance(Feedbacks.class);
            Marshaller mar = ctx.createMarshaller();
            mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
            File f = new File(context + "feedbackList.xml");
            mar.marshal(feedbacks, f);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    public boolean editProducts(String productName, String categoryID, String price, String description) {
//        try {
//            java.sql.Connection conn = null;
//            Config.Connection config = new Config.Connection();
//            conn = config.getConnection();
//            String sql = "Update Products Set productName = ?, categoryID = ?, price = ?, description";
//            PreparedStatement stm = conn.prepareStatement(sql);
//            stm.setString(1, productName);
//            stm.setString(2, categoryID);
//            stm.setString(3, price);
//            stm.setString(4, description);
//            int result = stm.executeUpdate();
//            stm.close();
//            if (result > 0) {
//                return true;
//            }
//        }
//        catch  (Exception e) {
//            e.printStackTrace();
//        }
//    return false;
//    }
    public void MarshallingOrder(String context) throws ClassNotFoundException, SQLException, JAXBException {

        Orders orders = new Orders();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        ResultSet rs = null;
        try {
            String sql = "Select * from Orders";
            PreparedStatement stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();

            while (rs.next()) {
                int orderID = rs.getInt("orderID");
                Date orderDate = rs.getDate("orderDate");
                String username = rs.getString("username");
                int orderTotal = rs.getInt("orderTotal");
                String address = rs.getString("address");
                String note = rs.getString("note");
                String statusID = rs.getString("statusID");

                OrderList order = new OrderList();
                order.setOrderID(new BigInteger(Integer.valueOf(orderID).toString()));
                order.setOrderDate(orderDate);
                order.setUsename(username);
                order.setOrderTotal(new BigInteger(Integer.valueOf(orderTotal).toString()));
                order.setAddress(address);
                order.setNote(note);
                order.setStatusID(statusID);

                orders.getOrder().add(order);


            }
            JAXBContext ctx = JAXBContext.newInstance(Orders.class);
            Marshaller mar = ctx.createMarshaller();
            mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
            File f = new File(context + "orderList.xml");
            mar.marshal(orders, f);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int insertProducts(String productName, int categoryID, String price, String productImg,
            boolean genderProduct, String description) {
        int productId = -1;
        try {
            java.sql.Connection conn = null;
            Config.Connection config = new Config.Connection();
            conn = config.getConnection();
            String sql = "Insert Into Products( productName, categoryID, price, genderProduct, productImg, description )"
                    + " Values (?,?,?,?,?,?)";
            PreparedStatement stm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stm.setString(1, productName);
            stm.setInt(2, categoryID);
            stm.setString(3, price);
            stm.setBoolean(4, genderProduct);
            stm.setString(5, productImg);
            stm.setString(6, description);
            stm.executeUpdate();
            ResultSet resultID = stm.getGeneratedKeys();
            while (resultID.next()) {
                productId = resultID.getInt(1);
            }
            stm.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return productId;
    }

    public boolean updateProducts(int productID, String productName, String price, String description) {
        try {
            java.sql.Connection conn = null;
            Config.Connection config = new Config.Connection();
            conn = config.getConnection();
            String sql = "Update Products set productName = ?, price = ?, description=? where productID=?";

            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setString(1, productName);
            stm.setString(2, price);
            stm.setString(3, description);
            stm.setInt(4, productID);
            int result = stm.executeUpdate();
            stm.close();
            conn.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        return false;
    }

    public boolean deleteProduct(int productID) {
        try {
            java.sql.Connection conn = null;
            Config.Connection config = new Config.Connection();
            conn = config.getConnection();
            String sql = "Delete from Products where productID=?";

            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setInt(1, productID);
            int result = stm.executeUpdate();
            stm.close();
            conn.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        return false;
    }

    public boolean deleteFB(int feedbackID) {
        try {
            java.sql.Connection conn = null;
            Config.Connection config = new Config.Connection();
            conn = config.getConnection();
            String sql = "Delete from Feedbacks where feedbackID=?";

            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setInt(1, feedbackID);
            int result = stm.executeUpdate();
            stm.close();
            conn.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        return false;
    }

    public boolean deleteOrder(int orderID) {
        try {
            java.sql.Connection conn = null;
            Config.Connection config = new Config.Connection();
            conn = config.getConnection();
            String sql = "Delete from Orders where orderID=?";

            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setInt(1, orderID);
            int result = stm.executeUpdate();
            stm.close();
            conn.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        return false;
    }

    public Products getAllProducts(String context) throws ClassNotFoundException, SQLException, JAXBException {
        Products products = new Products();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        ResultSet rs = null;
        try {
            String sql = "Select * from Products";
            PreparedStatement stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();

            while (rs.next()) {
                int productID = rs.getInt("productID");
                String productName = rs.getString("productName");
                int categoryID = rs.getInt("categoryID");
                long price = rs.getLong("price");
                String productImg = rs.getString("productImg");
                String description = rs.getString("description");
                Boolean genderProduct = rs.getBoolean("genderProduct");

                if (productImg == null) {
                    productImg = "Chưa có hình ảnh nào";
                }
                if (description == null) {
                    description = "Không có mô tả nào cho sản phẩm";
                }

                ProductList product = new ProductList();
                product.setProductID(new BigInteger(Integer.valueOf(productID).toString()));
                product.setProductName(productName);
                product.setCategoryID(new BigInteger(Integer.valueOf(categoryID).toString()));
                product.setPrice(new BigInteger(Long.valueOf(price).toString()));
                product.setProductImg(productImg);
                product.setDescription(description);
                product.setGenderProduct(genderProduct);
                products.getProduct().add(product);
            }
            return products;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
